package cn.web.management.dao.impl;

import cn.web.management.dao.inface.FindAllDao;
import cn.web.management.domain.*;
import cn.web.management.utils.JDBCUtil;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import java.util.List;
import java.util.Map;

public class FindAllImpl implements FindAllDao {
    private JdbcTemplate template = new JdbcTemplate(JDBCUtil.getDataSource());

    /**
     * @description: 查询用户账户密码
     * @paramName: []
     * @paramType: []
     * @returnType: java.util.List<cn.web.management.domain.UserInform>
     */
    public List<UserInform> findUserNamePassword() {
        //定义sql语句
        String sql = "select employno,username,userpassword,identity,userPortrait from personal_inform";

        //执行sql
        List<UserInform> list = template.query(sql, new BeanPropertyRowMapper<UserInform>(UserInform.class));
        return list;
    }

    /**
     * @description: 根据员工号查询个人信息
     * @paramName: [employno]
     * @paramType: [java.lang.String]
     * @returnType: java.util.Map<java.lang.String,java.lang.Object>
     */
    public Map<String, Object> findPersonalInform(String employno){
        String sql = "select * from personal_inform where employno = '" +employno+ '\'';
        Map<String, Object> stringObjectMap = template.queryForMap(sql);
        return stringObjectMap;
    }

    /**
     * @description: 根据时间查询个人工资
     * @paramName: [employno, startTime, endTime]
     * @paramType: [java.lang.String, java.lang.String, java.lang.String]
     * @returnType: java.util.List<cn.web.management.domain.Salary>
     */
    public List<Salary> findPersonSalary(String employno, String startTime, String endTime) {
        String sql = "select * from salary where employno = '" +employno+ "' and payoff_time between '"+startTime+"' and '"+endTime+"'";
        List<Salary> query = template.query(sql, new BeanPropertyRowMapper<Salary>(Salary.class));
        return query;
    }

    /**
     * @description: 查询个人所有工资
     * @paramName: [employno]
     * @paramType: [java.lang.String]
     * @returnType: java.util.List<cn.web.management.domain.Salary>
     */
    public List<Salary> findPersonSalary(String employno) {
        String sql = "select * from salary where employno = '"+employno+'\'';
        List<Salary> salaries = template.query(sql, new BeanPropertyRowMapper<Salary>(Salary.class));
        return salaries;
    }

    /**
     * @description: 按照工资单号查询工资单
     * @paramName: [payroll]
     * @paramType: [java.lang.String]
     * @returnType: java.util.List<cn.web.management.domain.Salary>
     */
    public List<Salary> findPayrollDetail(String payroll) {
        String sql = "select * from salary where payroll = '" + payroll +"'";
        List<Salary> query = template.query(sql, new BeanPropertyRowMapper<Salary>(Salary.class));
        return query;
    }

    /**
     * @description: 查询个人平均工资
     * @paramName: [employno]
     * @paramType: [java.lang.String]
     * @returnType: double
     */
    public double findPersonalAvgSalary(String employno){
        String sql = "select avg(salary_number) from salary where employno = '"+employno+"'";
        Map<String, Object> stringObjectMap = template.queryForMap(sql);
        Double avg_salary = (Double)stringObjectMap.get("avg(salary_number)");
        return avg_salary;
    }

    /**
     * @description: 根据部门号查询部门信息
     * @paramName: [departmentno]
     * @paramType: [java.lang.String]
     * @returnType: java.util.List<cn.web.management.domain.DepartmentInform>
     */
    public List<DepartmentInform> findDepartmentInform(String departmentno) {
        String sql = "select * from department_inform where departmentno = '"+departmentno+"'";
        List<DepartmentInform> query = template.query(sql, new BeanPropertyRowMapper<DepartmentInform>(DepartmentInform.class));
        return query;
    }

    /**
     * @description: 查询所有部门信息
     * @paramName: []
     * @paramType: []
     * @returnType: java.util.List<cn.web.management.domain.DepartmentInform>
     */
    public List<DepartmentInform> findDepartmentInform() {
        String sql = "select * from department_inform";
        List<DepartmentInform> query = template.query(sql, new BeanPropertyRowMapper<DepartmentInform>(DepartmentInform.class));
        return query;
    }

    /**
     * @description: 按照部门号查询小组信息
     * @paramName: [departmentno]
     * @paramType: [java.lang.String]
     * @returnType: java.util.List<cn.web.management.domain.GroupInform>
     */
    public List<GroupInform> findGroupDeportmentInform(String departmentno){
        String sql = "select * from group_inform where departmentno = \""+departmentno+"\"";
        List<GroupInform> query = template.query(sql, new BeanPropertyRowMapper<GroupInform>(GroupInform.class));
        return query;
    }

    /**
     * @description: 根据组号查询小组信息
     * @paramName: [groupno]
     * @paramType: [java.lang.String]
     * @returnType: java.util.List<cn.web.management.domain.GroupInform>
     */
    public List<GroupInform> findGroupInform(String groupno) {
        String sql = "select * from group_inform where groupno = '"+groupno+"'";
        List<GroupInform> query = template.query(sql, new BeanPropertyRowMapper<GroupInform>(GroupInform.class));
        return query;
    }

    /**
     * @description: 查询所有小组信息
     * @paramName: []
     * @paramType: []
     * @returnType: java.util.List<cn.web.management.domain.GroupInform>
     */
    public List<GroupInform> findGroupInform() {
        String sql = "select * from group_inform";
        List<GroupInform> query = template.query(sql, new BeanPropertyRowMapper<GroupInform>(GroupInform.class));
        return query;
    }

    /**
     * @description: 查询所有员工信息
     * @paramName: []
     * @paramType: []
     * @returnType: java.util.List<cn.web.management.domain.PersonalInform>
     */
    public List<PersonalInform> findAllPersonalInform() {
        String sql = "select * from personal_inform";
        List<PersonalInform> query = template.query(sql, new BeanPropertyRowMapper<PersonalInform>(PersonalInform.class));
        return query;
    }

    /**
     * @description: 按照小组号查询员工信息
     * @paramName: [groupno]
     * @paramType: [java.lang.String]
     * @returnType: java.util.Map<java.lang.String,java.lang.Object>
     */
    public List<PersonalInform> findGroupPersonalInform(String groupno) {
        String sql = "select * from personal_inform where groupno = '"+groupno+"'";
        List<PersonalInform> query = template.query(sql, new BeanPropertyRowMapper<PersonalInform>(PersonalInform.class));
        return query;
    }

    /**
     * @description: 按照部门号查询员工信息
     * @paramName: [departmentno]
     * @paramType: [java.lang.String]
     * @returnType: java.util.List<cn.web.management.domain.PersonalInform>
     */
    public List<PersonalInform> findDepartmentPersonalInform(String departmentno) {
        String sql = "select * from personal_inform where groupno in " +
                "(select groupno from group_inform where departmentno = '"+departmentno+"');";
        List<PersonalInform> query = template.query(sql, new BeanPropertyRowMapper<PersonalInform>(PersonalInform.class));
        return query;
    }

    /**
     * @description: 按照员工号查询员工信息
     * @paramName: [employno]
     * @paramType: [java.lang.String]
     * @returnType: java.util.List<cn.web.management.domain.PersonalInform>
     */
    public List<PersonalInform> findPersonalInform1(String employno){
        String sql = "select * from personal_inform where employno = '" +employno+ '\'';
        List<PersonalInform> query = template.query(sql, new BeanPropertyRowMapper<PersonalInform>(PersonalInform.class));
        return query;
    }

}
